
/**
	Author	    : Rohan
	Date	    : Aug 26th 2004
	Description : Returns the list of customers and their address Id's	
	Usage       : exec sp_get_customer_address_summary ''
			select * from Address where Address_Id = 746
*/
alter proc sp_get_customer_address_summary
(
@Customer_Name varchar(65)
)
as
begin

declare @customer_address_details Table( Row_Id int IDENTITY, Row_Description char(30), Customer_Id int, Address_Id char(4), Number varchar(50), Name_or_Address1 varchar(65), Dex_Row_Id char(4) )

--Inserting Customer Details
insert into @customer_address_details( Row_Description, Customer_Id, Address_Id, Number, Name_or_Address1, Dex_Row_Id ) select 'Customer', Customer.Customer_Id, '', Customer.C_Number, Customer.C_Name, ISNULL( convert( char, Gp_Ct_Customer_Mapping.Dex_Row_Id ), '' ) from Customer left join gp_ct_customer_mapping on Customer.Customer_Id = gp_ct_customer_mapping.Customer_Id where Customer.C_Name like @Customer_Name + '%' order by Customer.Customer_Id 

--Inserting Default Billing Address Details
insert into @customer_address_details( Row_Description, Customer_Id, Address_Id, Number, Name_or_Address1, Dex_Row_Id ) select 'Default Billing Address', Customer.Customer_Id, Customer.C_Default_Billing_Address_Id, '', Address.Address_1, ISNULL( convert( char, Gp_Ct_Address_Mapping.Dex_Row_Id ), '' ) from Customer join Address on Customer.C_Default_Billing_Address_Id = Address.Address_Id left join Gp_Ct_Address_Mapping on Customer.C_Default_Billing_Address_Id = Gp_Ct_Address_Mapping.Address_Id where Customer.C_Name like @Customer_Name + '%'

--Inserting Default Shipping Address Details
insert into @customer_address_details( Row_Description, Customer_Id, Address_Id, Number, Name_or_Address1, Dex_Row_Id ) select 'Default Shipping Address', Customer.Customer_Id, Customer.C_Default_Shipping_Address_Id, '', Address.Address_1, ISNULL( convert( char, Gp_Ct_Address_Mapping.Dex_Row_Id ), '' ) from Customer join Address on Customer.C_Default_Shipping_Address_Id = Address.Address_Id left join Gp_Ct_Address_Mapping on Customer.C_Default_Shipping_Address_Id = Gp_Ct_Address_Mapping.Address_Id where Customer.C_Name like @Customer_Name + '%'

--Inserting the Shipping Addresses
insert into @customer_address_details( Row_Description, Customer_Id, Address_Id, Number, Name_or_Address1, Dex_Row_Id ) select 'Shipping Address', Customer.Customer_Id, Address.Address_Id, '', Address.Address_1, ISNULL( convert( char, Gp_Ct_Address_Mapping.Dex_Row_Id ), '' ) from Customer join Customer_Shipping_Address_Table on Customer.Customer_Id = Customer_Shipping_Address_Table.Customer_Id join Address on Customer_Shipping_Address_Table.Address_Id = Address.Address_Id left join Gp_Ct_Address_Mapping on Gp_Ct_Address_Mapping.Address_id = Address.Address_Id where Address.Address_Id in ( select Address_Id from Customer_Shipping_Address_Table where Customer_ID = Customer.Customer_Id )  and Customer.C_Name like @Customer_Name + '%'

--Displaying the details
select Row_Description, Customer_Id, Address_Id, Number, Name_or_Address1, Dex_Row_Id from @customer_address_details order by Customer_Id, Row_Id

end --End of Proc

